import pymysql
import re
import threading
lock = threading.RLock() # 获取锁

def get_conn():
    # 连接信息
    db_config = {
        'host': 'localhost',
        'user': 'root',
        'port': 3306,
        'password': '123456',
        'db': 'jianzhimao',
        'charset': 'utf8'
    }
    db = pymysql.connect(**db_config)
    return db


def modifyData(sql):
    db = get_conn()
    cursor = db.cursor()
    try:
        lock.acquire()  # 加锁
        cursor.execute(sql)
        db.commit()
        lock.release()  # 解锁
        return True
    except Exception as e:
        db.rollback()
        print('对数据进行操作失败：', e)
        return False
    finally:
        db.close()


def queryData(sql, db=get_conn()):
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        result = cursor.fetchall()
        return result
    except Exception as e:
        db.rollback()
        print('查询数据失败：', e)
        return False


class CreateTable:

    @staticmethod
    def __table_exists(table_name, db=get_conn()):
        # 查询数据库下所有表
        show_sql = """show tables;"""
        cursor = db.cursor()
        cursor.execute(show_sql)
        table = cursor.fetchall()
        # 通过正则解析出列表名
        table_list = re.findall(r'\'(.*?)\',', str(table))
        # 判断表是否存在
        if table_name in table_list:
            return True
        return False

    def create_city_tbl(self):
        create_city_sql = """CREATE TABLE `city` (
        `id`  varchar(50) NOT NULL ,
        `name`  varchar(30) NOT NULL ,
        `url`  varchar(255) DEFAULT NULL ,
        `status`  int(1) NOT NULL DEFAULT 0 COMMENT '0表示未获取，1为以获取，-1为获取失败' ,
        PRIMARY KEY (`id`)
        )ENGINE=INNODB DEFAULT CHARSET=utf8
        ;"""
        if not self.__table_exists('city'):
            return modifyData(create_city_sql)
        print('city表已存在!')
        return False

    def create_area_tbl(self):
        create_area_sql = """CREATE TABLE `area` (
        `id`  varchar(50) NOT NULL ,
        `name`  varchar(30) NOT NULL ,
        `url`  varchar(255) DEFAULT NULL ,
        `city_id`  varchar(255) NOT NULL ,
        `status`  int(1) NOT NULL DEFAULT 0 COMMENT '0表示未获取，1为以获取，-1为获取失败' ,
        PRIMARY KEY (`id`)
        )ENGINE=INNODB DEFAULT CHARSET=utf8
        ;"""
        if not self.__table_exists('area'):
            return modifyData(create_area_sql)
        print('area表已存在!')
        return False

    def create_job_tbl(self, ):
        create_job_sql = """CREATE TABLE `job` (
        `id`  varchar(255) NOT NULL ,
        `title`  varchar(50) NOT NULL ,
        `url`  varchar(255) DEFAULT NULL ,
        `area_id`  varchar(255) NOT NULL ,
        `pv` varchar(10) NOT NULL ,
        `date`  datetime NOT NULL ,
        `type`  varchar(10) DEFAULT NULL ,
        `headcount`  varchar(50) DEFAULT NULL ,
        `address`  varchar(50) DEFAULT NULL ,
        `wage`  varchar(50) DEFAULT NULL ,
        `detail`  longtext DEFAULT NULL ,
        `company_name`  varchar(255) DEFAULT NULL ,
        `company_profile`  longtext DEFAULT NULL ,
        `company_address`  varchar(255) DEFAULT NULL ,
        `status`  int(1) NOT NULL DEFAULT 0 COMMENT '0表示未获取，1为以获取，-1为获取失败' ,
        PRIMARY KEY (`id`)
        )ENGINE=INNODB DEFAULT CHARSET=utf8
        ;"""
        if not self.__table_exists('job'):
            return modifyData(create_job_sql)
        print('job表已存在!')
        return False


class SaveData:

    def save_city(self, id, name, url):
        insert_sql = """insert ignore into city (id,name,url) 
        values ('{}','{}','{}')""".format(id, name, url)
        if not modifyData(insert_sql):
            print('error: save city data !')

    def save_area(self, id, name, url, city_id):
        insert_sql = """insert ignore into area (id,name,url,city_id) 
        values ('{}','{}','{}','{}')""".format(id, name, url, city_id)
        if not modifyData(insert_sql):
            print('error: save area data !')

    def save_job(self, id, title, url, area_id, pv, date):
        insert_sql = """insert ignore into job (id,title,url,area_id,pv,date) 
        values ('{}','{}','{}','{}','{}','{}')
        """.format(id, title, url, area_id, pv, date)
        if not modifyData(insert_sql):
            print('error: save job data !')


class QueryData:

    def select_col_tbl(self, table_name, *column_name, **where):
        # 判断列名是否为空
        if column_name:
            # 判断是否有传入条件
            if where:
                select_sql = """{}""".format(
                    'select ' + ','.join(list(column_name)) + ' from ' + table_name + ' where ' +
                    ["{}='{}'".format(col, data) for col, data in where.items()][0])
                query_data = queryData(select_sql)
                result = []
                for data in [list(i) for i in query_data]:
                    result.append({list(column_name)[i]: data[i] for i in range(len(data))})
                return result
            else:
                select_sql = """{}""".format('select ' + ','.join(list(column_name)) + ' from ' + table_name)
                query_data = queryData(select_sql)
                result = []
                for data in [list(i) for i in query_data]:
                    result.append({list(column_name)[i]: data[i] for i in range(len(data))})
                return result
        else:
            try:
                raise Exception('Error: no args,"column_name" ')
            except Exception as e:
                print(e)
            return False


class UpdateData:

    def upd_city_status(self, status, id):
        update_sql = """update city set status = {} 
        where id = '{}'""".format(status, id)
        if not modifyData(update_sql):
            print('error: update `status` from city !')

    def upd_area_status(self, status, id):
        update_sql = """update area set status = {} 
        where id = '{}'""".format(status, id)
        if not modifyData(update_sql):
            print('error: update `status` from area !')

    def upd_job_status(self, status, id):
        update_sql = """update job set status = {} 
                where id = '{}'""".format(status, id)
        if not modifyData(update_sql):
            print('error: update `status` from job !')

    def upd_job_info(self, job_type, job_headcount, job_address, job_wage, job_detail, com_name, com_profile,
                     com_address, status, id):
        update_sql = """update job set type = '{}',headcount = '{}',address = '{}',wage = '{}',detail = '{}',
        company_name = '{}',company_profile = '{}',company_address = '{}',status = {}
        where id = '{}'""".format(job_type, job_headcount, job_address, job_wage, job_detail, com_name, com_profile,
                                  com_address, status, id)
        if not modifyData(update_sql):
            print('error: update `status` from job !')


if __name__ == '__main__':
    c = CreateTable()
    c.create_job_tbl()
